﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataAccess {
    public class TransactionRepository : DataProvider {
        public int registerTrans(String transID, String SID, int productID, DateTime beginDate, DateTime endDate, int minQuanlity, int nextTransQuanlity) {
            //write to db
            String sql = "INSERT INTO Transactions(TransID, RegisterDate, SID, State, ProductID, BeginDate, EndDate, MinQuanlity, NextTransQuanlity) VALUES('{0}', '{1}', '{2}', {3}, {4}, '{5}', '{6}', {7}, {8})";
            sql = String.Format(sql, transID, DateTime.Now, SID, 0, productID, beginDate, endDate, minQuanlity, nextTransQuanlity);
            if (this.executeQuery(sql) > 0) {
                return 1;
            }
            return -1;
        }

        /// <summary>
        /// get transactions are waiting to accept (status = 0)
        /// </summary>
        /// <returns></returns>
        public DataTable getWaitingToAccept() {
            DataTable table = new DataTable();

            String sql = "SELECT t.ID, t.TransID, t.RegisterDate, t.SID, t.Status, l.PartnerID, p.Name, p.Email, p.Address, p.Tel " +
                         "FROM   Transactions AS t INNER JOIN " +
                                "LoginLog AS l ON t.SID = l.SID INNER JOIN " +
                                "Partners AS p ON l.PartnerID = p.PartnerID " +
                         "WHERE (t.Status = 0)";
            table = this.executeQueryDataTable(sql);
            return table;
        }

        public DataTable getWaitingToAccept(int partnerID) {
            DataTable table = new DataTable();

            String sql = "SELECT t.ID, t.TransID, t.RegisterDate, t.SID, t.Status, l.PartnerID, p.Name, p.Email, p.Address, p.Tel " +
                         "FROM   Transactions AS t INNER JOIN " +
                                "LoginLog AS l ON t.SID = l.SID INNER JOIN " +
                                "Partners AS p ON l.PartnerID = p.PartnerID " +
                         "WHERE (t.Status = 0) AND p.PartnerID = {0}";
            sql = String.Format(sql, partnerID);

            table = this.executeQueryDataTable(sql);
            return table;
        }

        /// <summary>
        /// get partner by transaction id
        /// </summary>
        /// <param name="transID"></param>
        /// <returns></returns>
        public DataTable getPartnerByTransID(int transID) {
            String sql = "SELECT Transactions.TransID, LoginLog.SID, LoginLog.PartnerID, Partners.PvdPwd, Partners.PvdID, Partners.LinkWS_UpdateInventory, Partners.LinkWS_CheckInventory, Partners.LinkWS_AcceptTrans, Partners.LinkWS_RefuseTrans, Partners.WS_Name, Partners.LinkWS_Login FROM LoginLog INNER JOIN Transactions ON LoginLog.SID = Transactions.SID INNER JOIN Partners ON LoginLog.PartnerID = Partners.PartnerID WHERE (Transactions.ID = {0})";
            sql = String.Format(sql, transID);

            return this.executeQueryDataTable(sql);
        }

        /// <summary>
        /// Update transaction status
        /// 0 - waitting
        /// 1 - accept successfully
        /// 2 - cancel trans
        /// 3 - accept trans error
        /// </summary>
        /// <param name="status"></param>
        public void updateTransactionState(int transID, int state) {
            String sql = String.Format("UPDATE Transactions SET State = {0} WHERE ID = {1}", state, transID);
            this.executeNonQuery(sql);
        }


        public DataTable getPendingTransaction(int partnerID = 0) {
            String sql = "SELECT   Transactions.RegisterDate, Partners.Name, Partners.Email, Partners.Address, Partners.Tel, Partners.PartnerLoginName, LoginLog.SID, Partners.PartnerID, Transactions.TransID, Transactions.ID, Products.ProductPhotoURL, Products.ProductName, Products.ProductID, Transactions.BeginDate, Transactions.EndDate FROM  Partners INNER JOIN LoginLog ON Partners.PartnerID = LoginLog.PartnerID INNER JOIN  Transactions ON LoginLog.SID = Transactions.SID INNER JOIN                          Products ON Transactions.ProductID = Products.ProductID WHERE  Transactions.State = 0 ";
            if (partnerID != 0)
                sql += " AND Partners.PartnerID = " + partnerID;
            return this.executeQueryDataTable(sql);
        }

        public DataTable getPartnerTransaction(int partnerID) {
            String sql = "SELECT Transactions.RegisterDate, Partners.Name, Partners.Email, Partners.Address, Partners.Tel, Partners.PartnerLoginName, LoginLog.SID, Partners.PartnerID, Transactions.TransID, Transactions.ID, Transactions.BeginDate, Transactions.EndDate, Products.ProductPhotoURL, Products.ProductName, Products.ProductID FROM            Partners INNER JOIN LoginLog ON Partners.PartnerID = LoginLog.PartnerID INNER JOIN Transactions ON LoginLog.SID = Transactions.SID INNER JOIN Products ON Transactions.ProductID = Products.ProductID WHERE Transactions.State = 1 AND Partners.PartnerID = {0} AND Transactions.EndDate > '{1}'";
            sql = string.Format(sql, partnerID, DateTime.Now);

            return this.executeQueryDataTable(sql);
        }

        public DataTable getCancelTransaction(int partnerID) {
            String sql = "SELECT Transactions.RegisterDate, Partners.Name, Partners.Email, Partners.Address, Partners.Tel, Partners.PartnerLoginName, LoginLog.SID, Partners.PartnerID, Transactions.TransID, Transactions.ID, Transactions.BeginDate, Transactions.EndDate, Products.ProductPhotoURL, Products.ProductName, Products.ProductID FROM            Partners INNER JOIN LoginLog ON Partners.PartnerID = LoginLog.PartnerID INNER JOIN Transactions ON LoginLog.SID = Transactions.SID INNER JOIN Products ON Transactions.ProductID = Products.ProductID " + 
                        " WHERE Transactions.State = 2 AND Partners.PartnerID = {0} OR Transactions.EndDate < '{1}'";
            sql = string.Format(sql, partnerID, DateTime.Now);

            return this.executeQueryDataTable(sql);
        }

        public DataTable getDetail(int id) {
            DataTable table = new DataTable();

            String sql = "SELECT Transactions.ID, Transactions.State, Transactions.TransID, Transactions.NextTransQuanlity, Transactions.MinQuanlity, Transactions.EndDate, Transactions.BeginDate, Products.ProductID, Products.ProductName, Products.Weight, Products.Height, Products.Width, Products.Length, Products.QuantityAtLeast, Products.ProductPrice, Transactions.RegisterDate, LoginLog.SID, Partners.PartnerID, Partners.Name, Partners.Email, Partners.Address, Partners.Tel, Partners.PartnerLoginName FROM Partners INNER JOIN LoginLog ON Partners.PartnerID = LoginLog.PartnerID INNER JOIN Transactions ON LoginLog.SID = Transactions.SID INNER JOIN  Products ON Transactions.ProductID = Products.ProductID WHERE        (Transactions.ID = {0})";
            sql = string.Format(sql, id);
            table = this.executeQueryDataTable(sql);
            return table;
        }

        public DataTable laySPChuaHopTac(int partnerID) {
            DataTable table = new DataTable();
            String sql = "SELECT        p.ProductID, p.CatID, p.ProductName, p.ProductPrice, p.ProductPhotoURL, p.ProductDescription, p.UpdateDate, p.QuantityAtLeast, p.OtherInfo, p.InStock, p.Length, p.Width, p.Height, p.Weight FROM            LoginLog INNER JOIN                         Transactions ON LoginLog.SID = Transactions.SID INNER JOIN                         Partners ON LoginLog.PartnerID = Partners.PartnerID CROSS JOIN                         Products AS p WHERE        (p.ProductID NOT IN                             (SELECT        ProductID                               FROM            Transactions AS t)) AND (Partners.PartnerID = {0}) ";
            sql = string.Format(sql, partnerID);
            table = this.executeQueryDataTable(sql);
            return table;
        }

    }
}
